package manager;

import java.util.ArrayList;

import entity.Course;
import entity.Student;

public class CompletedCourseMgr {

	private DatabaseMgr dbMgr;

	public CompletedCourseMgr() {
		dbMgr = new DatabaseMgr();
	}

	public ArrayList<Course> retrieveAllCompletedCourses() {

		String sql = "select * from completed_course inner join course on "
				+ "completed_course.courseCode = course.coursecode order by userId";

		return RetrieveCourseUtil.retrieveMultipleCoursesForQuery(dbMgr, sql);
	}

	public ArrayList<Student> retrieveStudentsWhoCompletedCourse(
			String courseCode) {

		String sql = "select * from completed_course inner join student on "
				+ "completed_course.userId = student.userId where coursecode=? ";

		return RetrieveStudentUtil.retrieveMultipleStudentsForQuery(dbMgr, sql,
				courseCode);
	}

	public void addCompletedCourseForStudent(String courseCode, String userId) {

		String sql = "insert into completed_course (userId, courseCode) values (?, ?)";

		dbMgr.openAndUpdateAndClose(sql, userId, courseCode);
	}

	public ArrayList<Course> retrieveCompletedCourseForStudent(String userId) {

		String sql = "select * from completed_course "
				+ "inner join student on student.userid = completed_course.userid"
				+ "inner join course on completed_course.courseCode = course.courseCode "
				+ "where completed_course.userid =? ";

		return RetrieveCourseUtil.retrieveMultipleCoursesForQuery(dbMgr, sql,
				userId);
	}

	public void deleteAllCompletedCourses() {
		dbMgr.openAndUpdateAndClose("delete from completed_course");
	}

}
